﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using Northwind.NET.Model;
using Northwind.NET.Settings;
using System.Data.Common;

namespace Northwind.NET.EDM.FE.WinForms.Controls
{
    public partial class ProductsUserControl: BaseUserControl
    {
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            LoadProducts();
            LoadCategories();
            LoadSuppliers();
        }
        public ProductsUserControl()
        {
            InitializeComponent();
        }

        public override string FormCaption { get { return Settings.Resources.ProductsFormCaption; } }
        public override dynamic GetEntitySet(ObjectContext context)
        {
            return new Model.Product();
        }

        public override BindingSource UserControlBindingSource { get { return productBindingSource; } }

        internal override void SetBindingSource()
        {
            // it has to be used here, if BaseUserControl's code is used then
            // categoryId binding fails
            productBindingSource.DataSource = GetEntitySet(null);

            supplierСomboBox.DataSource = new List<Model.Supplier>();
            categoryComboBox.DataSource = new List<Model.Category>();
            
        }
        private void LoadCategories()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                DbDataAdapter adapter = Factory.CreateDataAdapter();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT Category.ID AS CategoryID FROM Products WHERE ID = '" + this.iDTextBox.Text + "'";

                adapter.SelectCommand = command;

                DataSet dataset = new DataSet();
                dataset.Tables.Add("Categories");
                adapter.Fill(dataset.Tables["Categories"]);

                this.categoryComboBox.DataSource = dataset.Tables["Categories"].AsEnumerable().Select(x => x.ItemArray[0]).ToList();
            }
        }
        void OnMove(object sender, System.EventArgs e)
        {
            LoadCategories();
            LoadSuppliers();
        }
        private void LoadSuppliers()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                DbDataAdapter adapter = Factory.CreateDataAdapter();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT SupplierID AS SupplierID FROM Products WHERE ID = '" + this.iDTextBox.Text + "'";

                adapter.SelectCommand = command;

                DataSet dataset = new DataSet();
                dataset.Tables.Add("Suppliers");
                adapter.Fill(dataset.Tables["Suppliers"]);

                this.supplierСomboBox.DataSource = dataset.Tables["Suppliers"].AsEnumerable().Select(x => x.ItemArray[0]).ToList();
            }
        }
        private void LoadProducts()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                DbDataAdapter adapter = Factory.CreateDataAdapter();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT ID AS ProductID, Name, QuantityPerUnit, UnitPrice, UnitsInStock, " +
                                        "UnitsOnOrder, ReorderLevel, Discontinued, SupplierID, " + 
                                        "Category.ID FROM Products";

                adapter.SelectCommand = command;

                DataSet dataset = new DataSet();
                dataset.Tables.Add("Products");
                adapter.Fill(dataset.Tables["Products"]);

                this.productBindingSource.DataSource = dataset.Tables["Products"];


            }
        }
        private void previewProductListButton_Click(object sender, EventArgs e)
        {
            todo();
        }
    }
}